package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
*/

import dbPool.ConnectionDAO;
import beans.Customer;
import beans.CustomerLogin;
import beans.Movie;

public class MovieDAO {
	
	ConnectionDAO conn = new ConnectionDAO();
    Connection con = null;
    Statement stmt = null;
    
    static {
    	try{
    		new dbPool.JDCConnectionDriver("com.mysql.jdbc.Driver","jdbc:mysql://localhost/virtualreel", "root", "root"); 
    	}catch(Exception e){
    		
    	}
    }
    
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:jdc:jdcpool");
    }
    
    public int createMovie(Movie cBean){
    	try{
    		con = getConnection();
    		  		
    		Statement stmt = con.createStatement();
    		String query = "INSERT INTO `movie`(m_id, m_title, m_banner, m_release_year, m_rental_price, m_category, m_available_copies, m_description) VALUES('"+cBean.getM_Id()+"','"+cBean.getM_Title()+"','"+cBean.getM_Banner()+"','"+cBean.getM_Release_Year()+"','"+cBean.getM_Rental_Price()+"','"+cBean.getM_Category()+"','"+cBean.getM_Available_Copies()+"','"+cBean.getM_Description()+"');";
    		stmt.executeUpdate(query);
    		return 0;
    	}
    	catch(Exception se){
    		Logger.getLogger(MovieDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return 1;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public Movie retrieveMovie(String M_Id, String Column_Name){
    	Movie cBeanOne = null;
    	try{
    		con = getConnection();
    		
    		Statement stmt = con.createStatement();
    		cBeanOne = new Movie();
    		String query = "SELECT "+Column_Name+" FROM `movie` WHERE `m_id` LIKE '"+M_Id+"';";
    		ResultSet rs = stmt.executeQuery(query);
    		while(rs.next()){
    			cBeanOne.setM_Id(rs.getString("M_Id"));
    			cBeanOne.setM_Title(rs.getString("M_Title"));
    			cBeanOne.setM_Banner(rs.getString("M_Banner"));
    			cBeanOne.setM_Release_Year(rs.getString("M_Release_Year"));
    			cBeanOne.setM_Rental_Price(rs.getString("M_Rental_Price"));
    			cBeanOne.setM_Category(rs.getString("M_Category"));
    			cBeanOne.setM_Available_Copies(rs.getInt("M_Available_Copies"));
    			cBeanOne.setM_Description(rs.getString("M_Description"));
    		}
    		return cBeanOne;
    	}
    	catch(Exception se){
    		Logger.getLogger(MovieDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return cBeanOne;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public int updateMovie(String M_Id, String key, String value){
    	try{
    		con = getConnection();
			
    		Statement stmt = con.createStatement();
    		String query = "UPDATE `movie` SET  `"+key+"` =  '"+value+"' WHERE  `movie`.`m_id` =  '"+M_Id+"';";
    		stmt.executeUpdate(query);
    		return 0;
    	}
    	catch(Exception se){
    		Logger.getLogger(MovieDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return 1;
    	}
    	finally {
    		try {
				con.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public int deleteMovie(String M_Id) {
    	try{
    		con = getConnection();

    		Statement stmt = con.createStatement();
    		String query = "DELETE FROM `movie` WHERE `movie`.`m_id` = '"+M_Id+"';";
    		stmt.executeUpdate(query);
    		return 0;
    	}
    	catch(Exception se){
    		Logger.getLogger(MovieDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return 1;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public Movie[] retrieveAllMovies(){
    	Movie cBeanOne = null;
    	ArrayList<Movie> cBeanTwo = new ArrayList<Movie>();
    	Movie[] cBeanThree = null;
    	
    	try{
    		con = getConnection();
    		
    		Statement stmt = con.createStatement();
    		
    		String query = "SELECT * FROM `movie`;";
    		ResultSet rs = stmt.executeQuery(query);
    		while(rs.next()){
    			cBeanOne = new Movie();
    			cBeanOne.setM_Id(rs.getString("M_Id"));
    			cBeanOne.setM_Title(rs.getString("M_Title"));
    			cBeanOne.setM_Banner(rs.getString("M_Banner"));
    			cBeanOne.setM_Release_Year(rs.getString("M_Release_Year"));
    			cBeanOne.setM_Rental_Price(rs.getString("M_Rental_Price"));
    			cBeanOne.setM_Category(rs.getString("M_Category"));
    			cBeanOne.setM_Available_Copies(rs.getInt("M_Available_Copies"));
    			cBeanOne.setM_Description(rs.getString("M_Description"));
    			cBeanTwo.add(cBeanOne);
    		}
    		cBeanThree = new Movie[cBeanTwo.size()];
    		cBeanTwo.toArray(cBeanThree);
    		return cBeanThree;
    	}
    	catch(Exception se){
    		Logger.getLogger(MovieDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return cBeanThree;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public Movie[] searchMovies(String key, String value){
    	Movie cBeanOne = null;
    	ArrayList<Movie> cBeanTwo = new ArrayList<Movie>();
    	Movie[] cBeanThree = null;
    	
    	try{
    		con = getConnection();
    		Statement stmt = con.createStatement();
    		
    		String query = "SELECT * FROM `movie` WHERE `"+key+"` = '"+value+"';";
    		ResultSet rs = stmt.executeQuery(query);
    		while(rs.next()){
    			cBeanOne = new Movie();
    			cBeanOne.setM_Id(rs.getString("M_Id"));
    			cBeanOne.setM_Title(rs.getString("M_Title"));
    			cBeanOne.setM_Banner(rs.getString("M_Banner"));
    			cBeanOne.setM_Release_Year(rs.getString("M_Release_Year"));
    			cBeanOne.setM_Rental_Price(rs.getString("M_Rental_Price"));
    			cBeanOne.setM_Category(rs.getString("M_Category"));
    			cBeanOne.setM_Available_Copies(rs.getInt("M_Available_Copies"));
    			cBeanOne.setM_Description(rs.getString("M_Description"));
    			cBeanTwo.add(cBeanOne);
    		}
    		cBeanThree = new Movie[cBeanTwo.size()];
    		cBeanTwo.toArray(cBeanThree);
    		return cBeanThree;
    	}
    	catch(Exception se){
    		Logger.getLogger(MovieDAO.class.getName()).log(Level.SEVERE, "save failed", se);
    		return cBeanThree;
    	}
    	finally {
    		try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	}
    }
    
    public static void main(String args[]){
    	
    	//trial method one
    	/*Movie cBean = new Movie();
    	
    	cBean.setM_Id("mv1-db-0909");
    	cBean.setM_Title("Dil Chahta Hai ");
    	cBean.setM_Banner("Fox Studios");
    	cBean.setM_Category("Comedy");
    	cBean.setM_Description("adsfasfdassd");
    	cBean.setM_Release_Year(2004);
    	cBean.setM_Rental_Price(9.99f);
    	cBean.setM_Available_Copies(3);
    	
    	MovieDAO mdao = new MovieDAO();
    	
    	for(int i=1000; i<=9999; i++){
    		cBean.setM_Id("mv1-db-"+i);
    		cBean.setM_Title("Dil Chahta Hai "+i);
    		int result = mdao.createMovie(cBean);
        	System.out.println(result);
    	}*/
    	
    	
    	
    	
    	/*//trial method two
    	Movie cBean = new Movie();
    	MovieDAO mdao = new MovieDAO();
    	cBean = mdao.retrieveMovie("mv1-db-0909", "*");
    	System.out.println(cBean.getM_Title());*/
    	
    	
    	/*//trial method three
    	Movie cBean = new Movie();
    	MovieDAO mdao = new MovieDAO();
    	int result = mdao.updateMovie("mv1-db-0909", "m_title", "DCH");*/
    }

}
